﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace EIS_desktop
{
    public partial class Expenses : Form
    {
        #region fields
        private PayRoll prform;
        private bool newRowAdded = false;
        private int rownum;
    
        private SqlDataAdapter sqladp = new SqlDataAdapter();
        private SqlCommandBuilder sqlcmb = new SqlCommandBuilder();
        private DataTable dt = new DataTable();
        private SqlConnection con = new SqlConnection();
        #endregion

        #region methods
        /// <summary>
        /// default constructor
        /// </summary>
        public Expenses()
        {
            InitializeComponent();
        }

        /// <summary>
        /// constructor with payroll
        /// </summary>
        /// <param name="objpr"></param>
        public Expenses(PayRoll objpr)
        {
            InitializeComponent();
            prform = objpr;
        }
       
        /// <summary>
        /// Get list of expenses specified withint a period
        /// </summary>
        /// <returns></returns>
        private DataTable GetExpensesList()
        {
            string datestr = "1-" + prform.Month + "-" + prform.Year.ToString();

            System.Data.SqlClient.SqlConnection sqlcon = new System.Data.SqlClient.SqlConnection(System.Configuration.ConfigurationManager.AppSettings["constr"]);
            System.Data.SqlClient.SqlCommand sqlcmd = new System.Data.SqlClient.SqlCommand
                ("Select * from Expenses where EmpId=" + prform.EmployeeId + "and PayPeriod='" + datestr + "'", sqlcon);
            System.Data.SqlClient.SqlDataAdapter sqladp = new System.Data.SqlClient.SqlDataAdapter(sqlcmd);

            DataTable dt = new DataTable();
            sqladp.Fill(dt);
          
            return dt;
        }

        private void GetConnection( )
        {
            con = new System.Data.SqlClient.SqlConnection(System.Configuration.ConfigurationManager.AppSettings["constr"]);
        }
        
        private void GetData( )
        {
            string datestr = "1-" + prform.Month + "-" + prform.Year.ToString();
             System.Data.SqlClient.SqlConnection sqlcon = new System.Data.SqlClient.SqlConnection(System.Configuration.ConfigurationManager.AppSettings["constr"]);
             System.Data.SqlClient.SqlCommand sqlcmd = new System.Data.SqlClient.SqlCommand();
             string selectcmd = "Select * from Expenses where EmpId=" + prform.EmployeeId + "and PayPeriod='" + datestr + "'";
             sqladp = new SqlDataAdapter(selectcmd, sqlcon);
             sqlcmb = new SqlCommandBuilder(sqladp);
             sqladp.Fill(dt);
        }

        private bool DeleteRow(DataGridViewRow drow)
        {
            try
            {
                System.Data.SqlClient.SqlConnection sqlcon = new System.Data.SqlClient.SqlConnection(System.Configuration.ConfigurationManager.AppSettings["constr"]);
                System.Data.SqlClient.SqlCommand sqlcmd = new System.Data.SqlClient.SqlCommand();
                sqlcmd.CommandText = "delete from Expenses where empid= @empid and PayPeriod = @payperiod and  ExpenseAmount =@amount and FilingFeeDeductions = @ffdeduct";
                sqlcmd.Connection = sqlcon;
                System.Data.SqlClient.SqlParameter sqlparam = new System.Data.SqlClient.SqlParameter("@empid", drow.Cells[0].Value);
                sqlcmd.Parameters.Add(sqlparam);
                sqlparam = new System.Data.SqlClient.SqlParameter("@payperiod", drow.Cells[1].Value);
                sqlcmd.Parameters.Add(sqlparam);
                sqlparam = new System.Data.SqlClient.SqlParameter("@amount", drow.Cells[2].Value ?? DBNull.Value);
                sqlcmd.Parameters.Add(sqlparam);
                sqlparam = new System.Data.SqlClient.SqlParameter("@ffdeduct", drow.Cells[2].Value ?? DBNull.Value);
                sqlcmd.Parameters.Add(sqlparam);
                sqlparam = new System.Data.SqlClient.SqlParameter("@desc", drow.Cells[3].Value ?? DBNull.Value);
                sqlcmd.Parameters.Add(sqlparam);
                sqlcmd.CommandType = CommandType.Text;
                sqlcon.Open();
                int success = sqlcmd.ExecuteNonQuery();
                sqlcon.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
            return true;
        }
        #endregion

        private void btnClose_Click(object sender, EventArgs e)
        {
            double  totalexpenses = 0;
            foreach (DataGridViewRow dgvrow in dataGridView1.Rows)
            {
                if (dgvrow.Cells["ExpenseAmount"].Value == System.DBNull.Value)
                totalexpenses = totalexpenses +default(double);
                else
                    totalexpenses = totalexpenses + Convert.ToDouble(dgvrow.Cells["ExpenseAmount"].Value);
            }
            foreach (DataGridViewRow dgvrow in dataGridView1.Rows)
            {
                if (dgvrow.Cells["FilingFeeDeductions"].Value ==System.DBNull.Value )
                totalexpenses = totalexpenses + default(double);
                else
                    totalexpenses = totalexpenses + Convert.ToDouble(dgvrow.Cells["FilingFeeDeductions"].Value);


            }
            prform.AddToDeductions(totalexpenses);
            this.Close();
        }

        private void Expenses_Load(object sender, EventArgs e)
        {
            dataGridView1.DataSource = GetExpensesList();
            dataGridView1.Columns[1].DefaultCellStyle.Format = "dd-MMM-yyyy";
            GetData();
        }

        private void dataGridView1_UserAddedRow(object sender, DataGridViewRowEventArgs e)
        {
            newRowAdded = true;
            rownum = e.Row.Index -1;
        }

        private void dataGridView1_RowLeave(object sender, DataGridViewCellEventArgs e)
                                {
            if (newRowAdded)
            {
               
            }
            newRowAdded = false;
        }

        private void dataGridView1_CellBeginEdit(object sender, DataGridViewCellCancelEventArgs e)
        {
            string datestr = "1-" + prform.Month + "-" + prform.Year.ToString();
            if (e.ColumnIndex == 0)
                dataGridView1.CurrentRow.Cells[e.ColumnIndex].Value = prform.EmployeeId;
            if (e.ColumnIndex == 1)
                dataGridView1.CurrentRow.Cells[e.ColumnIndex].Value = datestr;
            //if (e.ColumnIndex == 1)
            //{
            //    dateTimePicker1.Location = dataGridView1.GetCellDisplayRectangle(e.ColumnIndex, e.RowIndex, false).Location;
            //    dateTimePicker1.Visible = true;
            //}
        }


       
        private void btnsave_Click(object sender, EventArgs e)
        {
            try
            {
                DataGridViewRow drow = dataGridView1.CurrentRow;
                if (drow.Index != rownum) return;

                System.Data.SqlClient.SqlConnection sqlcon = new System.Data.SqlClient.SqlConnection(System.Configuration.ConfigurationManager.AppSettings["constr"]);
                System.Data.SqlClient.SqlCommand sqlcmd = new System.Data.SqlClient.SqlCommand("insert Expenses values (@empid,@payperiod,@amount,@ffdeduct,@desc)",sqlcon);
                System.Data.SqlClient.SqlParameter sqlparam =new System.Data.SqlClient.SqlParameter("@empid", drow.Cells[0].Value);
                sqlcmd.Parameters.Add(sqlparam);
               sqlparam=new System.Data.SqlClient.SqlParameter("@payperiod",drow.Cells[1].Value);
                sqlcmd.Parameters.Add(sqlparam);
                sqlparam=new System.Data.SqlClient.SqlParameter("@amount",drow.Cells[2].Value??DBNull.Value);
                sqlcmd.Parameters.Add(sqlparam);
                sqlparam =new System.Data.SqlClient.SqlParameter("@ffdeduct",drow.Cells[2].Value??DBNull.Value);
                sqlcmd.Parameters.Add(sqlparam);
                sqlparam=new System.Data.SqlClient.SqlParameter("@desc",drow.Cells[3].Value??DBNull.Value);
                sqlcmd.Parameters.Add(sqlparam);
                sqlcmd.CommandType = CommandType.Text;
                sqlcon.Open();
                int success = sqlcmd.ExecuteNonQuery();
                sqlcon.Close();
                dataGridView1.Refresh();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void btndelete_Click(object sender, EventArgs e)
        {
             DialogResult dlgresult = MessageBox.Show("Are your sure you want to delete the row?", "delete row", MessageBoxButtons.YesNo);
            if (dlgresult == DialogResult.Yes)
            {
                DeleteRow(dataGridView1.CurrentRow);
            }
            Expenses_Load(sender, e);
        }

       

       

       
    }
}
